 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL.SQLStatements
{
    public class clsQueryBuilder
    {
        #region --- Variables
        // Receive  connection string from web.config
        private string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["hoangkhanh"].ToString();
        #endregion --- Variables

        #region --- Properties
        private string table;
        public string propTable
        {
            get { return table; }
            set { table = value; }
        }

        private string column;
        public string propColumn
        {
            get { return column; }
            set { column = value; }
        }

        private string condition;
        public string propWhere
        {
            get { return condition; }
            set { condition = value; }
        }

        public string OrderByColumn
        {
            get;
            set;
        }

        public bool SortByDESC
        {
            get;
            set;
        }

        public bool SortByASC
        {
            get;
            set;
        }

        #endregion --- Properties

        #region --- Generate Select Query---
        private string GenerateSelectOrderBy()
        {
            string rs = "";
            if (SortByDESC==true)
            {
                rs = "Select " + column + " From " + table + " Where " + condition + " Order By " + OrderByColumn + " DESC ";
            }
            else if(SortByASC==true)
            {
                rs = "Select " + column + " From " + table + " Where " + condition + " Order By " + OrderByColumn + " ASC ";
            }
            return rs;
        }
        #endregion --- Generate Select Query---

        #region --- Public Methods to Execute SQL statements
        // This method to get data table 
        public DataTable getDatatable()
        {
            string queryString ="";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connectionString); // Create a new connection and new command
            if (OrderByColumn == null)
            {
                queryString = "Select " + column + " From " + table + " Where " + condition;  // Query string
            }
            else
            {
                queryString = GenerateSelectOrderBy();
            }

            try
            {
                conn.Open(); // Open connection
                SqlCommand cmd = new SqlCommand(queryString, conn); // New command to execute query after checked
                SqlDataReader rd = cmd.ExecuteReader(); // Executing
                dt.Load(rd); // Receive data table
                conn.Close(); // Close connetion
            }
            catch (Exception crap)
            {
                throw new Exception(crap.Message);
            }
            
            return dt; // retrun data
        }

        // This method going to execute non reader statement to checking
        public bool getBoolValue()
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string queryString = "Select " + column + " From " + table + " Where " + condition + "";  // Query string

            try
            {
                conn.Open(); // Open connection
                SqlCommand cmd = new SqlCommand(queryString, conn); // New command to execute query after checked
                SqlDataReader rd = cmd.ExecuteReader(); // Executing
                if (rd.Read()) // Return true if checking method is success
                {
                    rd.Close();
                    return true;
                }
                else // Return false if checking is fail
                {
                    rd.Close();
                    return false;
                }
            }
            catch(Exception crap)
            {
                throw new Exception(crap.Message);
            }

        }

        // Public Method to Run Insert into query
        public bool executeInsertInto(string TableName, Dictionary<String,Object> Data)
        {
            string columns = "";
            string values = "";
            Boolean insertStatus = true;
            foreach(KeyValuePair<String, Object> val in Data)
            {
                columns += String.Format("{0},",val.Key.ToString());
                values += String.Format("{0},",val.Value);

            }
            columns = columns.Substring(0, columns.Length -1);
            values = values.Substring(0, values.Length-1);
            try
            {
                this.ExecuteNonQuery(String.Format("Insert into {0}({1}) Values({2});", TableName, columns, values));
            }
            catch(Exception crap)
            {
                insertStatus = false; // Insert fail
                throw new Exception(crap.Message);
                
            }
            return insertStatus;

        }

        // Public Method to Run Update Query
        public bool executeUpdate(string TableName, Dictionary<String, Object> Data, string Where)
        {
            string vals = "";
            Boolean updateStatus = true;
            if (Data.Count >= 1)
            {
	            foreach (KeyValuePair<String, Object> val in Data)
                {
	                vals += String.Format(" {0} = {1},", val.Key.ToString(), val.Value.ToString());
	            }
	            vals = vals.Substring(0, vals.Length - 1);
	        }
	        try
	        {
	            this.ExecuteNonQuery(String.Format("Update {0} Set {1} Where {2};", TableName, vals, Where));
	        }
	        catch
	        {
	            updateStatus = false;
	        }
            return updateStatus;
        }
        #endregion --- Public Methods to Execute SQL statemments

        #region --- Private Methods
        private int ExecuteNonQuery(string queryString)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(queryString,conn);
            int rowUpdated = cmd.ExecuteNonQuery();
            conn.Close();
            return rowUpdated;
        }
        #endregion --- Private Methods

    }
}